﻿using Entity;
using IRealize;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace Realize
{
    public class CFSK : ICFSK
    {
        public async Task<int> AddCFSKAsync(Config_File_Second_Kind sk, string sqlserver)
        {
            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {
                string sql = $@"insert into [dbo].[config_file_second_kind]([first_kind_id], [first_kind_name], [second_kind_id], [second_kind_name], [second_salary_id], [second_sale_id])
                                values('{sk.first_kind_id}','{sk.first_kind_name}','{sk.second_kind_id}','{sk.second_kind_name}','{sk.second_salary_id}','{sk.second_sale_id}')";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        public async Task<int> DeleteCFSKAsync(int fsk_id, string sqlserver)
        {
            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {

                string sql = $@"delete from [dbo].[config_file_second_kind] where [fsk_id]='{fsk_id}'";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        public async Task<Config_File_Second_Kind?> SelectCFSKByIDAsync(string sqlserver, string secondKindId)
        {
            string sql = "SELECT * FROM config_file_second_kind WHERE second_kind_id = @SecondKindId";
            using (var connection = new SqlConnection(sqlserver))
            {
                await connection.OpenAsync();
                return await connection.QueryFirstOrDefaultAsync<Config_File_Second_Kind>(
                    sql, new { SecondKindId = secondKindId }
                );
            }
        }

        public async Task<List<Config_File_Second_Kind>> SelectCFSKAsync(string sqlserver)
        {
            Task<List<Config_File_Second_Kind>> task = Task<List<Config_File_Second_Kind>>.Run(() =>
            {
                using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
                {

                    string sql = $@"select * from [dbo].[config_file_second_kind]";

                    return sqlConnection.Query<Config_File_Second_Kind>(sql).ToList();
                }

            });

            return await task;
        }

        public async Task<Config_File_Second_Kind?> SelectCFSKIDAsync(int fsk_id, string sqlserver)
        {
            Task<Config_File_Second_Kind?> task = Task<Config_File_Second_Kind>.Run(() =>
            {
                using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
                {

                    string sql = $@" select * from [dbo].[config_file_second_kind] where [fsk_id]='{fsk_id}' ";

                    return sqlConnection.Query<Config_File_Second_Kind>(sql).FirstOrDefault();
                }

            });

            return await task;
        }

        public async Task<List<Config_File_Second_Kind>> SelectCFSKLBAsync(string sqlserver, string firstKindName)
        {
            string sql = @"
        SELECT b.second_kind_name 
        FROM config_file_first_kind a 
        INNER JOIN config_file_second_kind b 
        ON a.first_kind_id = b.first_kind_id 
        WHERE a.first_kind_name = @FirstKindName";

            using (var connection = new SqlConnection(sqlserver))
            {
                await connection.OpenAsync();
                // 1. 等待QueryAsync完成，获取IEnumerable<T>
                var enumerables = await connection.QueryAsync<Config_File_Second_Kind>(
                    sql,
                    new { FirstKindName = firstKindName }
                );
                // 2. 同步转换为List<T>
                return enumerables.ToList();
            }

        }

        public async Task<int> UpdateCFSKAsync(Config_File_Second_Kind fsk, string sqlserver, int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {

                string sql = $@"Update [dbo].[config_file_second_kind] set  [first_kind_id]='{fsk.first_kind_id}', [first_kind_name]='{fsk.first_kind_name}', [second_kind_id]='{fsk.second_kind_id}', [second_kind_name]='{fsk.second_kind_name}', [second_salary_id]='{fsk.second_salary_id}', [second_sale_id]='{fsk.second_sale_id}' where [fsk_id]='{id}'";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

       

    }
}
